package com.apes.scm.rbac.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.hr.base.model.WorkPost;
import com.apes.scm.masterdata.frame.model.Branch;
import com.apes.scm.masterdata.frame.model.Store;
import com.apes.scm.rbac.model.LocalPermit;
import com.apes.scm.rbac.model.Post;
import com.apes.scm.rbac.model.Role;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

public interface PostRepository extends CustomRepository<Post, String> {

    @Query(value = "select b.* from aut_post_users a,aut_posts b,aut_users c where a.post_id = b.id and a.user_id = c.id and b.merchant_management = 1 and c.origin = :id", nativeQuery = true)
    List<Post> findManagementPost(@Param("id") String id);

    @Query("select T from Post T where id like %:value%  or name like %:value% ")
    Page<Post> find(@Param("value") String value, Pageable pageable);

    List<Post> findAllByBranchAndWorkPost(Branch branch, WorkPost workPost);

    List<Post> findAllByBranchIdAndWorkPostId(String branchId, String workPostId);


    List<Post> findAllByBranchId(String id);

    List<Post> findAllByWorkPostId(String id);
    List<Post> findAllByOrigin(String origin);


    @Query("select distinct e.branch from Post e where e.workPost = :workPost")
    List<Branch> findDistinctBranchByWorkPost(@Param("workPost") WorkPost workPost);


//    @Query("select T from Post T where lastUpdatedStamp > :times or (id > :id and lastUpdatedStamp = :times) order by lastUpdatedStamp, id asc ")
//    List<Post> findByLastUpdatedStampAndId(@Param("times") Date times, @Param("id") String id, Pageable pageable);

    @Query("select t from Post t inner join t.roles r where r.id=?1")
    Set<Post> findAllByRole(@Param("id") String  role);

    @Query("select s " +
            "from Post s where to_char(s.lastUpdatedStamp,'yyyy-mm-dd hh24:mi:ss.ff3' ) > :times or (s.id > :id and to_char(s.lastUpdatedStamp,'yyyy-mm-dd hh24:mi:ss.ff3' ) = :times)  " +
            " order by s.lastUpdatedStamp, s.id asc ")
    List<Post> findByLastUpdatedStampAndId(@Param("times") String times, @Param("id") String id, Pageable pageable);

    List<Post> findAllByLocalPermitId(String origin);


    @Query(value = "select t.* from aut_posts t  " +
            " where " +
            " exists (select 1 from aut_role_posts c where c.post_id  = t.id" +
            "           and  c.role_id = :roleId)" +
            " and t.local_permit_id = :localPermitId " , nativeQuery = true)
    List<Post> findByLocalPermitIdAndRoleId(@Param("localPermitId") String localPermitId, @Param("roleId") String roleId);

    @Query(value = "select t.* from aut_posts t  " +
            " where " +
            " exists (select 1 from aut_role_posts c where c.post_id  = t.id" +
            "           and  c.role_id in :roleIds)" +
            " and t.local_permit_id = :localPermitId " , nativeQuery = true)
    List<Post> findByLocalPermitIdAndRoleIds(@Param("localPermitId") String localPermitId, @Param("roleIds") List<String> roleIds);

    @Query(value = "SELECT ROLE3.ORG_ID as ID\n" +
            "        FROM AUT_POSTS ROLE1,\n" +
            "        AUT_LOCAL_PERMIT ROLE2,\n" +
            "        AUT_LOCAL_PERMIT_ITEM ROLE3\n" +
            "       WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID\n" +
            "           AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID\n" +
            "           AND ROLE1.ID = :postId\n" +
            "           AND ROLE3.TYPE = 'pt_company'\n" +
            "           AND ROLE3.PURPOSE = 'pp_search'" , nativeQuery = true)
    List<Map> findLocalRoleByPost(@Param("postId") String postId);
}
